********************************************************************************
*This dofile load and cleans county-level insurance data from RMA-SOB
********************************************************************************

clear all
cd "$root"

**# Import and calculate the county-level ratio of irrigation land in all croplant in year 1997
forvalues yr = 1999(1)2024 {
	import delimited "data\raw\RMA_SOB\sobcov`yr'.txt", clear 

	ren	v1	year
	ren	v2	st_fips
	ren	v3	state
	ren	v4	cty_fips
	ren	v5	cty_name
	ren	v6	crop_code
	ren	v7	crop_name
	ren	v8	plan_code
	ren	v9	plan_name
	ren	v10	cover_cat
	ren	v11	delivery
	ren	v12	cover_level
	ren	v13	n_policies
	ren	v14	n_policies_acre
	ren	v15	n_policies_loss
	ren	v16	units_insured
	ren	v17	units_indemned
	ren	v18	unit
	ren	v19	insured_acres
	ren	v20	endorsed
	ren	v21	liability
	ren	v22	premium
	ren	v23	subsidy
	ren	v24	subsidy_st_pr
	ren	v25	subsidy_others
	ren	v26	efa_discount
	ren	v27	indemnity
	ren	v28	loss_ratio

	gen fips = st_fips*1000 + cty_fips
	keep if crop_code == 41 | crop_code == 81  // 41: corn, 81: soybeans

	gen weighted_cover = cover_level*insured_acres

	keep year fips crop_code premium subsidy insured_acres weighted_cover

	collapse year (rawsum) weighted_cover insured_acres premium subsidy, by(fips crop_code)

	replace weighted_cover = weighted_cover/insured_acres
	gen subsidy_ratio = subsidy/premium

	if year == 1999 {
			save "data\temp\insurance.dta", replace
		}
		else append using "data\temp\insurance.dta"
	save "data\temp\insurance.dta", replace
}

preserve
keep if crop_code == 41
rename (weighted_cover insured_acres premium subsidy subsidy_ratio) ///
(corn_weighted_cover corn_insured_acres corn_premium corn_subsidy corn_subsidy_ratio)
save "data\temp\insurance_corn.dta", replace
restore

keep if crop_code == 81
rename (weighted_cover insured_acres premium subsidy subsidy_ratio) ///
(soybeans_weighted_cover soybeans_insured_acres soybeans_premium soybeans_subsidy soybeans_subsidy_ratio)

merge 1:1 fips year using data\temp\insurance_corn.dta, nogen
erase data\temp\insurance_corn.dta

save "data\temp\insurance.dta", replace